package imse.team1.inventory.dal;

import imse.team1.orders.data.OrderItem;
import org.hsqldb.jdbc.JDBCDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.concurrent.Semaphore;

/**
 * @author Eugen, 6/18/12 7:42 PM
 */
public class InventoryQueryService {
    private static DataSource ds;
    private final static String INVENTORYDB_LOCATION = "localhost";
    private final static Connection[] connectionPool = new Connection[5];
    private final static Semaphore[] connectionLock = new Semaphore[connectionPool.length];
    private static Random random = new Random();

    static {
        JDBCDataSource dataSource = new JDBCDataSource();
        dataSource.setUrl("jdbc:hsqldb:hsql://" + INVENTORYDB_LOCATION + "/wineinventory");
        dataSource.setUser("TEAM1");
        dataSource.setPassword("bpelproject");
        ds = dataSource;
        for (int i = 0; i < connectionPool.length; i++) {
            try {
                connectionPool[i] = ds.getConnection();
            } catch (SQLException e) {
                System.out.println("Global Inventory: Couldn't get connection #" + i);
                e.printStackTrace();
            }
        }
        for (int i = 0; i < connectionPool.length; i++) {
            connectionLock[i] = new Semaphore(1);
        }
        Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {
            public void run() {
                try {
                    for (int i = 0; i < connectionPool.length; i++) {
                        connectionPool[i].close();
                    }
                } catch (SQLException ex) {
                    System.out.println(ex);
                }
                for (int i = 0; i < connectionLock.length; i++) {
                    connectionLock[i].release();
                }
            }
        }));
    }

    private static class SQLRepo {
        private static final String subtractAvailableQuantity = "UPDATE bpelproject.@table SET qty = qty - ? where name = ?";
        private static final String addBookedQuantity = "UPDATE bpelproject.@table SET bookedqty = bookedqty + ? where name = ?";
        private static final String subtractBookedQuantity = "UPDATE bpelproject.@table SET qty = qty + ? where name = ?";
        private static final String addAvailableQuantity = "UPDATE bpelproject.@table SET bookedqty = bookedqty - ? where name = ?";
        private static final String getAvailableQuantity = "SELECT qty FROM bpelproject.@table WHERE name = ?";
        private static final String getQuantities = "SELECT qty, bookedqty FROM bpelproject.@table WHERE name = ?";
    }

    private static int getConnection() throws Exception {
        int i = random.nextInt(connectionPool.length);
        try {
            connectionLock[i].acquire();
            if (connectionPool[i].isClosed()) {
                synchronized (InventoryQueryService.class) {
                    if (connectionPool[i].isClosed()) {
                        System.out.println("Global Inventory: Connection " + i + " was closed!");
                        connectionPool[i] = ds.getConnection();
                        return i;
                    }
                }
            }
            return i;
        } catch (Exception ex) {
            connectionLock[i].release();
            throw ex;
        }
    }

    /**
     * @param items
     * @param warehouseID should be the city name where warehouse is located
     * @return the items that could not be booked and the missing quantity. Items that could be partially booked will be booked in the warehouse
     */
    public List<OrderItem> bookItems(List<OrderItem> items, String warehouseID) throws Exception {
        String tableName = "\"" + warehouseID + "Inventory\"";
        String subtractStmtTemp = new String(SQLRepo.subtractAvailableQuantity);
        String addStmtTemp = new String(SQLRepo.addBookedQuantity);
        subtractStmtTemp = subtractStmtTemp.replace("@table", tableName);
        addStmtTemp = addStmtTemp.replace("@table", tableName);
        List<OrderItem> missingItems = new ArrayList<OrderItem>(1);
        int index = getConnection();
        Connection conn = connectionPool[index];
        PreparedStatement subtractStmt = null, addStmt = null;
        try {
            conn.setAutoCommit(false);
            subtractStmt = conn.prepareStatement(subtractStmtTemp);
            addStmt = conn.prepareStatement(addStmtTemp);
            nextItem:
            for (OrderItem item : items) {
                try {
                    subtractStmt.setInt(1, item.getQty());
                    subtractStmt.setString(2, item.getName());
                    addStmt.setInt(1, item.getQty());
                    addStmt.setString(2, item.getName());
                    int updatedRows = subtractStmt.executeUpdate();
                    if (updatedRows == 0) {
                        OrderItem mItem = new OrderItem();
                        mItem.setName(item.getName());
                        mItem.setQty(item.getQty());
                        missingItems.add(mItem);
                    } else {
                        addStmt.executeUpdate();
                    }
                    conn.commit();
                } catch (SQLIntegrityConstraintViolationException ex) {
                    String getAvailableQuantityTemp = new String(SQLRepo.getAvailableQuantity);
                    getAvailableQuantityTemp = getAvailableQuantityTemp.replace("@table", tableName);
                    PreparedStatement stmt = conn.prepareStatement(getAvailableQuantityTemp);
                    stmt.setString(1, item.getName());
                    boolean bookedRemainingQuantity = false;
                    int availableQuantity = 0;
                    while (!bookedRemainingQuantity) {// try to book all the available quantity (since the requested one is greater than the available)
                        ResultSet rs = stmt.executeQuery();
                        rs.next();
                        availableQuantity = rs.getInt("qty");
                        if (availableQuantity == 0)   // not possible to book item in this inventory anymore
                        {  // for optimization only
                            OrderItem mItem = new OrderItem();
                            mItem.setName(item.getName());
                            missingItems.add(mItem);
                            mItem.setQty(item.getQty());
                            break nextItem;
                        }
                        subtractStmt.setInt(1, availableQuantity);
                        addStmt.setInt(1, availableQuantity);
                        try {
                            subtractStmt.executeUpdate();// try to book all the available quantity until succeed
                            addStmt.executeUpdate();
                            conn.commit();
                            bookedRemainingQuantity = true;
                        } catch (SQLIntegrityConstraintViolationException ex1) {// retry if in the meantime the available quantity decreased
                            continue;
                        }
                    }
                    OrderItem mItem = new OrderItem();
                    mItem.setName(item.getName());
                    missingItems.add(mItem);
                    mItem.setQty(item.getQty() - availableQuantity);
                }
            }
        } finally {
            conn.setAutoCommit(true);
            connectionLock[index].release();
        }
        if (missingItems.size() == 0)
            return null;
        else
            return missingItems;
    }

    public void removeBookedItems(List<OrderItem> items, String warehouseID) throws Exception {
        String tableName = "\"" + warehouseID + "Inventory\"";
        String subtractStmtTemp = new String(SQLRepo.subtractBookedQuantity);
        String addStmtTemp = new String(SQLRepo.addAvailableQuantity);
        subtractStmtTemp = subtractStmtTemp.replace("@table", tableName);
        addStmtTemp = addStmtTemp.replace("@table", tableName);
        List<OrderItem> missingItems = new ArrayList<OrderItem>(1);
        int index = getConnection();
        Connection conn = connectionPool[index];
        PreparedStatement subtractStmt = null, addStmt = null;
        try {
            conn.setAutoCommit(false);
            subtractStmt = conn.prepareStatement(subtractStmtTemp);
            addStmt = conn.prepareStatement(addStmtTemp);
            for (OrderItem item : items) {
                try {
                    subtractStmt.setInt(1, item.getQty());
                    subtractStmt.setString(2, item.getName());
                    subtractStmt.execute();
                    addStmt.setInt(1, item.getQty());
                    addStmt.setString(2, item.getName());
                    addStmt.execute();
                    conn.commit();
                } catch (SQLIntegrityConstraintViolationException ex) {
                    conn.rollback();// revert previously made updates
                    throw new Exception("Unexpected error happened while reverting the inventory changes in warehouse: " + warehouseID);
                }
            }
        } finally {
            conn.setAutoCommit(true);
            connectionLock[index].release();
        }
        return;
    }

    int[] getQuantitiesForWine(String warehouseID, String wineName) throws Exception {
        int[] qtys = new int[2];
        int index = getConnection();
        String getQuantitiesTemp = SQLRepo.getQuantities;
        getQuantitiesTemp = getQuantitiesTemp.replace("@table", "\"" + warehouseID + "Inventory\"");
        try {
            Connection conn = connectionPool[index];
            PreparedStatement stmt = conn.prepareStatement(getQuantitiesTemp);
            stmt.setString(1, wineName);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            qtys[0] = rs.getInt("qty");
            qtys[1] = rs.getInt("bookedqty");
        } finally {
            connectionLock[index].release();
        }
        return qtys;
    }
}
